云原生数据仓库AnalyticDB PostgreSQL版支持通过OSS外部表(即gpossext功能),将数据并行导出到阿里云对象存储OSS,并支持通过GZIP进行OSS外部表文件压缩,大量节省存储空间及成本。
功能介绍
目前gpossext支持读写TEXT、CSV格式的文件以及GZIP压缩格式的TEXT、CSV文件。
gpossext架构图如下。
TEXT和CSV格式说明
下列几个参数可以在外表DDL参数中指定,用于规定读写OSS的文件格式:
TEXT和CSV行分割符号是
\n
,也就是换行符。DELIMITER用于定义列的分割符:
当用户数据中包括DELIMITER时,则需要和QUOTE参数一同使用。
推荐的列分割符有
,
、\t
、|
或一些不常见的字符。
QUOTE用于包裹有特殊字符的用户数据(以列为单位):
包含有特殊字符的字符串会被QUOTE包裹,用于区分用户数据和控制字符。
如果不必要,例如整数,基于优化效率的考虑,不必使用QUOTE包裹数据。
QUOTE不能和DELIMITER相同,默认QUOTE是双引号。
当用户数据中包含了QUOTE字符,则需要使用转义字符ESCAPE加以区分。
ESCAPE用于特殊字符转义:
转义字符出现在需要转义的特殊字符前,表示它不是一个特殊字符。
ESCAPE默认和QUOTE相同,为双引号
""
。也支持设置成
\
(MySQL默认的转义字符)或别的字符。
控制字符和格式 | TEXT | CSV |
DELIMITER(列分割符) | \t(Tab) | , (Comma) |
QUOTE(摘引) | " (Double-Quote) | "(Double-Quote) |
ESCAPE(转义) | (不适用) | 与QUOTE相同 |
NULL(空值) | \N(Backslash-N) | (无引号的空字符串) |
所有的控制字符都必须是单字节字符。
注意事项
创建和使用外部表的语法,除了location相关参数,其余参数和Greenplum的使用方式相同。
数据导入导出的性能和AnalyticDB PostgreSQL的资源(CPU、I/O、内存、网络等)有关,也和OSS相关。为了获取最优的导入导出性能,建议在创建表时,使用列式存储加压缩功能。例如,指定子句
"WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)"
,详细信息,请参见 Greenplum Database创建表官方文档。为了保证数据导入导出的性能,请保证OSS与AnalyticDB PostgreSQL在同一地域下。
操作步骤
创建OSS外部表插件。
使用OSS外部表时,需要在AnalyticDB PostgreSQL中先创建OSS外部表插件(每个库中均需要单独创建)。创建命令如下:
CREATE EXTENSION IF NOT EXISTS oss_ext;
在AnalyticDB PostgreSQL中,创建WRITABLE外部表。
创建OSS外部表语法如下。
CREATE WRITABLE EXTERNAL TABLE table_name ( column_name data_type [, ...] | LIKE other_table ) LOCATION ('ossprotocol') FORMAT 'TEXT' [( [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] )] | 'CSV' [([QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE QUOTE column [, ...]] ] [ESCAPE [AS] 'escape'] )] [ ENCODING 'encoding' ] [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ] ossprotocol: oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name] id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
参数说明如下。
参数
说明
WRITABLE
WRITABLE是导出模式外部表的关键字,创建外部表时需要明确指明。
FORMAT
支持文件格式,例如TEXT、CSV。
ENCODING
文件中数据的编码格式,例如UTF8。
DISTRIBUTED BY
DISTRIBUTED BY子句可以让数据节点(Segment)按指定的分布键将数据导出到OSS。
oss://oss_endpoint
协议和Endpoint,格式为
协议名://oss_endpoint
,其中协议名为oss,oss_endpoint为OSS对应区域的域名。示例如下:oss://oss-cn-hangzhou.aliyuncs.com
重要如果是从阿里云的主机访问数据库,应该使用内网域名(即带有
internal
的域名),避免产生公网流量。id
阿里云账号的AccessKey ID。获取AccessKey操作,请参见创建AccessKey。
key
阿里云账号的AccessKey Secret。获取AccessKey操作,请参见创建AccessKey。
bucket
指定数据文件所在的Bucket,需要在OSS上预先创建。
prefix
指定数据文件对应路径名的前缀,不支持正则表达式,仅是匹配前缀。
说明与dir互斥,两者只能设置其中一个。
WRITABLE外部表在导出数据时,会根据该前缀自动生成一个唯一的文件名来给导出文件命名。
如果指定prefix=osstest/exp/outfromhdb,则表示的导出路径为osstest/exp/,导出文件的名称都以outfromhdb开头。
dir
OSS中的虚拟文件夹路径。
说明与prefix互斥,两者只能设置其中一个。
文件夹路径需要以
/
结尾,如test/mydir/
。在导出数据时,使用此参数创建外部表,所有数据会导出到此目录下的多个文件中,输出文件名的形式为
filename.x
,x为数字,但可能不是连续的。
compressiontype
导出文件的压缩格式。
none(默认值):导入的文件未压缩。
gzip:导入的文件压缩格式为GZIP。
说明目前仅支持GZIP压缩格式。
num_parallel_worker
设置导出到OSS的压缩数据的并行压缩线程个数,默认值为3。取值范围为1~8。示例如下:
num_parallel_worker=3
oss_flush_block_size
单次导出数据到OSS的buffer大小,默认为32 MB。取值范围为1 MB~128 MB。示例如下:
oss_flush_block_size=32
oss_file_max_size
设置导出到OSS的最大文件大小,超出之后会切换到另一个文件继续写。默认为1024 MB。取值范围为8 MB~4000 MB。示例如下:
oss_file_max_size=1024
oss_connect_timeout
设置连接超时。单位为秒,默认为10秒。
oss_dns_cache_timeout
设置DNS超时。单位为秒,默认为60秒。
oss_speed_limit
设置触发超时的最小速率。默认为1024字节,即1 KB。
需要与oss_speed_time参数配合使用。
说明如果使用默认值且连续15秒的传输速率小于1 KB,会触发超时。具体信息,请参见OSS SDK 错误处理。
oss_speed_time
设置触发超时的最长时间。默认为15秒。
需要与oss_speed_limit参数配合使用。
说明如果使用默认值且连续15秒的传输速率小于1 KB,会触发超时。具体信息,请参见OSS SDK 错误处理。
并行导出数据。
在AnalyticDB PostgreSQL数据库中执行如下命令,并行将数据导出到OSS。
INSERT INTO <外部表> SELECT * FROM <源表>
操作示例
本文以源表example为例,介绍将源表example的数据导出到OSS。
创建OSS外部表插件。
创建命令如下:
CREATE EXTENSION IF NOT EXISTS oss_ext;
创建源表,用于装载待导出的数据。
源表example的建表语句如下:
CREATE TABLE example (date text, time text, open float, high float, low float, volume int) DISTRIBUTED BY (date);
创建OSS导出外部表。
创建外部表时,使用prefix参数指定导出路径。示例如下:
CREATE WRITABLE EXTERNAL TABLE ossexample_exp (date text, time text, open float, high float, low float, volume int) location('oss://oss-cn-hangzhou.aliyuncs.com prefix=osstest/exp/outfromhdb id=XXX key=XXX bucket=testbucket') FORMAT 'csv' DISTRIBUTED BY (date);
创建外部表时,使用dir参数指定导出路径。示例如下:
CREATE WRITABLE EXTERNAL TABLE ossexample_exp (date text, time text, open float, high float, low float, volume int) location('oss://oss-cn-hangzhou.aliyuncs.com dir=osstest/exp/ id=XXX key=XXX bucket=testbucket') FORMAT 'csv' DISTRIBUTED BY (date);
将数据并行地从example表导出到OSS。
INSERT INTO ossexample_exp SELECT * FROM example;
执行如下查询计划,可以看到Segment节点直接将本地数据导出到OSS,没有进行数据重分布。
EXPLAIN INSERT INTO ossexample_exp SELECT * FROM example;
返回信息如下:
QUERY PLAN
---------------------------------------------------------------
Insert (slice0; segments: 3) (rows=1 width=92)
-> Seq Scan on example (cost=0.00..0.00 rows=1 width=92)
(2 rows)
SDK错误处理
当导入或导出操作出错时,错误日志可能会出现如下信息:
code:出错请求的HTTP状态码。
error_code:OSS的错误码。
error_msg:OSS的错误信息。
req_id:标识该次请求的UUID。当您无法解决问题时,可以凭req_id来请求OSS开发工程师的帮助。
具体信息,请参见OSS API 错误响应,超时相关的错误可以使用oss_ext相关参数处理。